SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted	NUMBER;
	l_total_imported	NUMBER;
	l_total_extracted_2	NUMBER;
	l_total_imported_2	NUMBER;				
	l_func_id			VARCHAR2(100):='CAPPOREC_POSITION';
	l_code VARCHAR2(100):=UPPER('&1');

BEGIN
	SELECT COUNT(1)
	INTO l_total_extracted        
	FROM   PER_ALL_POSITIONS pap,HR_ORGANIZATION_UNITS hou
	WHERE  pap.business_group_id = hou.business_group_id 
	AND    upper(hou.name) = 'BG_'||upper(substr(l_code, -4));
	/*and upper(hou.name) =(	SELECT 'BG_'||upper(to_char(set_of_books_id))
          					FROM   gl_sets_of_books
     						WHERE  upper(short_name) = upper(l_code));*/
	--AND    upper(hou.name) = upper(l_code);        						
	
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_PER_ALL_POSITIONS;

	SELECT COUNT(1)
	INTO l_total_extracted_2        
	FROM   HR_ALL_POSITIONS_F_TL
       WHERE  POSITION_ID IN (SELECT pap.POSITION_ID
					FROM   PER_ALL_POSITIONS pap,HR_ORGANIZATION_UNITS hou
					WHERE  pap.business_group_id = hou.business_group_id 
					AND    upper(hou.name) = 'BG_'||upper(substr(l_code, -4))
					/*and upper(hou.name) =(	SELECT 'BG_'||upper(to_char(set_of_books_id))
          					FROM   gl_sets_of_books
     						WHERE  upper(short_name) = upper(l_code))*/
					--AND    upper(hou.name) = upper(l_code)     						
				     );    
	
	SELECT COUNT(1) 
	INTO l_total_imported_2
	FROM CRST_HR_ALL_POSITIONS_F_TL;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));
	
	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
	
	dbms_output.put_line('Total Extracted_2: '||TO_CHAR(l_total_extracted_2)) ;
	dbms_output.put_line('Total Imported_2:  '||TO_CHAR(l_total_imported_2));
	
	IF l_total_extracted_2 <> l_total_imported_2 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
